﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using IHISCETeim.Library;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using IHISCETeim.Model;

namespace IHISCETeim.DAL
{
    public  class ODailyDAL
    {
        /// <summary>
        /// 添新日报表
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static int Add(ODailyInfo obj)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("ODaily_Add", obj.UserName, obj.WorkDate, obj.GongWei, obj.JobCode, obj.JobTitle, obj.SerialNumber, obj.TaskTime, obj.Remark,obj.DailyCategory,obj.MachineType);
            return Convert.ToInt32(DB.ExecuteScalar(dbCommand));
        }
        /// <summary>
        /// 删除日报表
        /// </summary>
        /// <param name="DailyId"></param>
        /// <returns></returns>
        public static bool Delete(int DailyId)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("ODaily_Delete", DailyId);
            try
            {
                DB.ExecuteScalar(dbCommand);
                return true;
            }
            catch
            {
                return false;
            }           
        }
        
        /// <summary>
        /// 日报表分页
        /// </summary>
        /// <param name="UserName"></param>
        /// <param name="PageIndex"></param>
        /// <param name="PageSize"></param>
        /// <param name="Sort"></param>
        /// <param name="TotalCount"></param>
        /// <returns></returns>
        public static DataTable GetPagination(string UserName,string SerialNumber,string GongWei, int PageIndex, int PageSize, string Sort, ref int TotalCount)
	    {
		    Database DB = DatabaseFactory.CreateDatabase();		
		
            DbCommand dbCommand = DB.GetStoredProcCommand("ODaily_GetPagination");
            DB.AddInParameter(dbCommand, "UserName", DbType.String, UserName);
            DB.AddInParameter(dbCommand, "SerialNumber", DbType.String, SerialNumber);
            DB.AddInParameter(dbCommand, "GongWei", DbType.String, GongWei);
            DB.AddInParameter(dbCommand, "PageIndex", DbType.Int32,PageIndex);
            DB.AddInParameter(dbCommand, "PageSize", DbType.Int32,PageSize);
            DB.AddInParameter(dbCommand, "Sort", DbType.String,Sort);
            DB.AddOutParameter(dbCommand, "TotalCount", DbType.Int32,8);
		    DataTable dt=  DB.ExecuteDataSet(dbCommand).Tables[0];
				
            TotalCount=Convert.ToInt32(DB.GetParameterValue(dbCommand,"TotalCount"));
            return dt;
	    }
        /// <summary>
        /// 修改日报表记录
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static bool Update(ODailyInfo obj)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("ODaily_Update", obj.DailyId, obj.UserName, obj.WorkDate, obj.GongWei, obj.JobCode, obj.JobTitle, obj.SerialNumber, obj.TaskTime, obj.Remark);
            try
            {
                DB.ExecuteScalar(dbCommand);
                return true;
            }
            catch (Exception exc)
            {
                return false;
            }
        }

        public static IDataReader GetById(int DailyId)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("ODaily_GetById", DailyId);
            return DB.ExecuteReader(dbCommand);
        }

        #region 报表部份
        /// <summary>
        /// 根据机号获取其所有的作业日报表记录
        /// </summary>
        /// <param name="SerialNumber"></param>
        /// <returns></returns>
        public static IDataReader GetListBySerialNumber(string SerialNumber)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("ODaily_GetListBySerialNumber", SerialNumber);
            return DB.ExecuteReader(dbCommand);
        }
        #endregion


        #region 报表部份
        public static DataTableCollection GetMachineTaskTimeChart(string WorkDateForm, string WorkDateTo)
        {
            Database DB = DatabaseFactory.CreateDatabase();
            //string sql = "SELECT  SerialNumber,SUM(TaskTime) TaskTime FROM dbo.ODaily_V WHERE JobType='直接' AND WorkDate BETWEEN @WorkDateForm AND @WorkDateTo GROUP BY SerialNumber ";
            DbCommand dbCommand = DB.GetStoredProcCommand("ODaily_GetMachineTaskTimeChart", WorkDateForm, WorkDateTo);
            return DB.ExecuteDataSet(dbCommand).Tables;
        }
        /// <summary>
        /// 获取直接间接工时
        /// </summary>
        /// <param name="WorkDateForm"></param>
        /// <param name="WorkDateTo"></param>
        /// <param name="jobtype">直接,间接</param>
        /// <returns></returns>
        public static double GetZJJJTaskTime(string WorkDateForm, string WorkDateTo, string jobtype)
        {
            Database DB = DatabaseFactory.CreateDatabase();
            //string sql = "SELECT  SerialNumber,SUM(TaskTime) TaskTime FROM dbo.ODaily_V WHERE JobType='直接' AND WorkDate BETWEEN @WorkDateForm AND @WorkDateTo GROUP BY SerialNumber ";
            DbCommand dbCommand = DB.GetStoredProcCommand("ODaily_GetZJJJTaskTime", WorkDateForm, WorkDateTo, jobtype);
            object x=DB.ExecuteDataSet(dbCommand).Tables[0].Rows[0][0];
            return Convert.ToDouble(x is DBNull?0:x);
        }
        /// <summary>
        /// 获取每台机器负担工时
        /// </summary>
        /// <param name="WorkDateForm"></param>
        /// <param name="WorkDateTo"></param>
        /// <returns></returns>
        public static DataTableCollection GetMachineTaskTime2(string WorkDateForm, string WorkDateTo)
        {
            Database DB = DatabaseFactory.CreateDatabase();
            //string sql = "SELECT  SerialNumber,SUM(TaskTime) TaskTime FROM dbo.ODaily_V WHERE JobType='直接' AND WorkDate BETWEEN @WorkDateForm AND @WorkDateTo GROUP BY SerialNumber ";
            DbCommand dbCommand = DB.GetStoredProcCommand("ODaily_GetMachineTaskTime2", WorkDateForm, WorkDateTo);
            return DB.ExecuteDataSet(dbCommand).Tables;
        }
        /// <summary>
        /// 获取每台机器负担工时
        /// </summary>
        /// <param name="WorkDateForm"></param>
        /// <param name="WorkDateTo"></param>
        /// <returns></returns>
        public static DataTable GetMachineTaskTime3(string WorkDateForm, string WorkDateTo)
        {
            Database DB = DatabaseFactory.CreateDatabase();
            //string sql = "SELECT  SerialNumber,SUM(TaskTime) TaskTime FROM dbo.ODaily_V WHERE JobType='直接' AND WorkDate BETWEEN @WorkDateForm AND @WorkDateTo GROUP BY SerialNumber ";
            DbCommand dbCommand = DB.GetStoredProcCommand("ODaily_GetMachineTaskTime3", WorkDateForm, WorkDateTo);
            return DB.ExecuteDataSet(dbCommand).Tables[0];
        }
        /// <summary>
        /// 按工位统计工时
        /// </summary>
        /// <param name="WorkDateForm"></param>
        /// <param name="WorkDateTo"></param>
        /// <returns></returns>
        public static DataTable GetMachineTaskTime4(string WorkDateForm, string WorkDateTo)
        {
            Database DB = DatabaseFactory.CreateDatabase();
            //string sql = "SELECT  SerialNumber,SUM(TaskTime) TaskTime FROM dbo.ODaily_V WHERE JobType='直接' AND WorkDate BETWEEN @WorkDateForm AND @WorkDateTo GROUP BY SerialNumber ";
            DbCommand dbCommand = DB.GetStoredProcCommand("ODaily_GetMachineTaskTime4", WorkDateForm, WorkDateTo);
            return DB.ExecuteDataSet(dbCommand).Tables[0];
        }

        /// <summary>
        /// 按项目统计工位工时
        /// </summary>
        /// <param name="WorkDateForm"></param>
        /// <param name="WorkDateTo"></param>
        /// <returns></returns>
        public static DataTable GetMachineTaskTime4_GroupBySerialNumber(string SerialNumber)
        {
            Database DB = DatabaseFactory.CreateDatabase();
            //string sql = "SELECT  SerialNumber,SUM(TaskTime) TaskTime FROM dbo.ODaily_V WHERE JobType='直接' AND WorkDate BETWEEN @WorkDateForm AND @WorkDateTo GROUP BY SerialNumber ";
            DbCommand dbCommand = DB.GetStoredProcCommand("ODaily_GetMachineTaskTime4_GroupBySerialNumber", "", "", SerialNumber);
            return DB.ExecuteDataSet(dbCommand).Tables[0];
        }

        /// <summary>
        /// 按项目获取各工位所耗工时
        /// </summary>
        /// <param name="SerialNumber"></param>
        /// <returns></returns>
        public static DataTable GetMachineTaskTime4_GroupByGongWei(string SerialNumber)
        {
            Database DB = DatabaseFactory.CreateDatabase();
            //string sql = "SELECT  SerialNumber,SUM(TaskTime) TaskTime FROM dbo.ODaily_V WHERE JobType='直接' AND WorkDate BETWEEN @WorkDateForm AND @WorkDateTo GROUP BY SerialNumber ";
            DbCommand dbCommand = DB.GetStoredProcCommand("ODaily_GetMachineTaskTime4_GroupByGongWei", SerialNumber);
            return DB.ExecuteDataSet(dbCommand).Tables[0];
        }
        /// <summary>
        /// 按项目统计直接工时对比
        /// </summary>
        /// <param name="WorkDateForm"></param>
        /// <param name="WorkDateTo"></param>
        /// <returns></returns>
        public static DataTable GetMachineTaskTime5(string WorkDateForm, string WorkDateTo)
        {
            Database DB = DatabaseFactory.CreateDatabase();
            //string sql = "SELECT  SerialNumber,SUM(TaskTime) TaskTime FROM dbo.ODaily_V WHERE JobType='直接' AND WorkDate BETWEEN @WorkDateForm AND @WorkDateTo GROUP BY SerialNumber ";
            DbCommand dbCommand = DB.GetStoredProcCommand("ODaily_GetMachineTaskTime5", WorkDateForm, WorkDateTo);
            return DB.ExecuteDataSet(dbCommand).Tables[0];
        }

        /// <summary>
        /// 按月统计工作内容对比表
        /// </summary>
        /// <param name="WorkDateForm"></param>
        /// <param name="WorkDateTo"></param>
        /// <returns></returns>
        public static DataTable GetMachineTaskTime6(string WorkDateForm, string WorkDateTo)
        {
            Database DB = DatabaseFactory.CreateDatabase();
            //string sql = "SELECT  SerialNumber,SUM(TaskTime) TaskTime FROM dbo.ODaily_V WHERE JobType='直接' AND WorkDate BETWEEN @WorkDateForm AND @WorkDateTo GROUP BY SerialNumber ";
            DbCommand dbCommand = DB.GetStoredProcCommand("ODaily_GetMachineTaskTime6", WorkDateForm, WorkDateTo);
            return DB.ExecuteDataSet(dbCommand).Tables[0];
        }
        #endregion

        #region 质量管理部 工时统计报表
        public static DataTableCollection ZGGetReport1(string WorkDateForm, string WorkDateTo)
        {
            Database DB = DatabaseFactory.CreateDatabase();
            //string sql = "SELECT  SerialNumber,SUM(TaskTime) TaskTime FROM dbo.ODaily_V WHERE JobType='直接' AND WorkDate BETWEEN @WorkDateForm AND @WorkDateTo GROUP BY SerialNumber ";
            DbCommand dbCommand = DB.GetStoredProcCommand("ODaily_ZGGetReport1", WorkDateForm, WorkDateTo);
            return DB.ExecuteDataSet(dbCommand).Tables;
        }

        /// <summary>
        /// 每月直接间接工时对比
        /// </summary>
        /// <param name="WorkDateForm"></param>
        /// <param name="WorkDateTo"></param>
        /// <returns></returns>
        public static DataTableCollection ZGGetReport2(string WorkDateForm, string WorkDateTo)
        {
            Database DB = DatabaseFactory.CreateDatabase();
            //string sql = "SELECT  SerialNumber,SUM(TaskTime) TaskTime FROM dbo.ODaily_V WHERE JobType='直接' AND WorkDate BETWEEN @WorkDateForm AND @WorkDateTo GROUP BY SerialNumber ";
            DbCommand dbCommand = DB.GetStoredProcCommand("ODaily_ZGGetReport2", WorkDateForm, WorkDateTo);
            return DB.ExecuteDataSet(dbCommand).Tables;
        }

        /// <summary>
        /// 按人员统计工时比
        /// </summary>
        /// <param name="WorkDateForm"></param>
        /// <param name="WorkDateTo"></param>
        /// <returns></returns>
        public static DataTable ZGGetReport4(string WorkDateForm, string WorkDateTo)
        {
            Database DB = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = DB.GetStoredProcCommand("ODaily_ZGGetReport4", WorkDateForm, WorkDateTo);
            return DB.ExecuteDataSet(dbCommand).Tables[0];
        }

        /// <summary>
        /// 按人员统计工时比_每个人员的工作内容时间
        /// </summary>
        /// <param name="WorkDateForm"></param>
        /// <param name="WorkDateTo"></param>
        /// <returns></returns>
        public static DataTable ZGGetReport4_GroupByRealName(string WorkDateForm, string WorkDateTo, string RealName)
        {
            Database DB = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = DB.GetStoredProcCommand("ODaily_ZGGetReport4_GroupByRealName", WorkDateForm, WorkDateTo,RealName);
            return DB.ExecuteDataSet(dbCommand).Tables[0];
        }

        /// <summary>
        /// 各机型统计直接工时对比
        /// </summary>
        /// <param name="WorkDateForm"></param>
        /// <param name="WorkDateTo"></param>
        /// <returns></returns>
        public static DataTable ZGReport5(string WorkDateForm, string WorkDateTo)
        {
            Database DB = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = DB.GetStoredProcCommand("ODaily_ZGReport5", WorkDateForm, WorkDateTo);
            return DB.ExecuteDataSet(dbCommand).Tables[0];
        }

        /// <summary>
        /// 按月统计工作内容对比表
        /// </summary>
        /// <param name="WorkDateForm"></param>
        /// <param name="WorkDateTo"></param>
        /// <returns></returns>
        public static DataTable ZGReport6(string WorkDateForm, string WorkDateTo)
        {
            Database DB = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = DB.GetStoredProcCommand("ODaily_ZGReport6", WorkDateForm, WorkDateTo);
            return DB.ExecuteDataSet(dbCommand).Tables[0];
        }
        #endregion

    }
}
